#!/usr/bin/env node

/**
 * Comprehensive Field Definitions Analysis Script
 * Extracts all field definitions from the three Excel CSV files and provides
 * a structured analysis of all 78 tables with their complete field structures.
 */

const fs = require('fs');
const path = require('path');

// File paths
const FILES = {
  base: '/home/abc/delivery-record-fronted/excel/06-21/属性表-基础参数表.csv',
  naming: '/home/abc/delivery-record-fronted/excel/06-21/属性表-对象命名表.csv',
  position: '/home/abc/delivery-record-fronted/excel/06-21/属性表-位置信息表.csv'
};

// Data source mapping
const DATA_SOURCE_MAPPING = {
  'P': 'Procurement (采购)',
  'E': 'Design (设计)',
  'M': 'Monitoring (监测)',
  'C': 'Construction (施工)',
  'O': 'Build (建设)',
  'R': 'Operations (运维)'
};

// Data type mapping
const DATA_TYPE_MAPPING = {
  '字符型': 'TEXT',
  '数值型': 'REAL',
  '日期型': 'TEXT', // Usually stored as TEXT in SQLite
  '整数型': 'INTEGER',
  '布尔型': 'INTEGER' // SQLite stores booleans as integers
};

class FieldDefinitionAnalyzer {
  constructor() {
    this.tables = new Map();
    this.statistics = {
      totalTables: 0,
      totalFields: 0,
      dataSourceCounts: {},
      dataTypeCounts: {},
      measurementTypeCounts: {}
    };
  }

  /**
   * Remove BOM character from the beginning of content
   */
  removeBOM(content) {
    if (content.charCodeAt(0) === 0xFEFF) {
      return content.slice(1);
    }
    return content;
  }

  /**
   * Parse CSV content with proper handling of multi-line fields and special characters
   */
  parseCSV(content) {
    // Simple CSV parsing - split by lines and handle comma-separated values
    const lines = content.split('\n').map(line => line.trim()).filter(line => line.length > 0);
    const result = [];
    
    for (let line of lines) {
      // Split by comma, but be careful with quotes
      const row = [];
      let current = '';
      let inQuotes = false;
      
      for (let i = 0; i < line.length; i++) {
        const char = line[i];
        
        if (char === '"') {
          inQuotes = !inQuotes;
          current += char;
        } else if (char === ',' && !inQuotes) {
          row.push(current.trim().replace(/^"|"$/g, ''));
          current = '';
        } else {
          current += char;
        }
      }
      
      // Add the last field
      row.push(current.trim().replace(/^"|"$/g, ''));
      
      // Only add non-empty rows
      if (row.some(cell => cell.length > 0)) {
        result.push(row);
      }
    }
    
    return result;
  }

  /**
   * Extract table information from a table header line
   */
  extractTableInfo(headerLine) {
    // Pattern: 表C.14    BAS008 橇基础参数表
    const match = headerLine.match(/表\s*C\.(\d+)\s+([A-Z]+\d+)\s+(.+)/);
    if (match) {
      return {
        tableNumber: `C.${match[1]}`,
        tableCode: match[2],
        tableName: match[3].trim(),
        fullTableName: `${match[2]}_params`
      };
    }
    
    // Alternative pattern: 表 C.2    NAM002 对象命名表
    const altMatch = headerLine.match(/表\s+C\.(\d+)\s+([A-Z]+\d+)\s+(.+)/);
    if (altMatch) {
      return {
        tableNumber: `C.${altMatch[1]}`,
        tableCode: altMatch[2],
        tableName: altMatch[3].trim(),
        fullTableName: `${altMatch[2]}_params`
      };
    }
    
    return null;
  }

  /**
   * Process a single CSV file and extract table definitions
   */
  processFile(filePath, fileType) {
    console.log(`\n🔍 Processing ${fileType} file: ${path.basename(filePath)}`);
    
    try {
      const content = fs.readFileSync(filePath, 'utf8');
      const cleanContent = this.removeBOM(content);
      const rows = this.parseCSV(cleanContent);
      
      let currentTable = null;
      let beforeCount = this.tables.size;
      
      for (let i = 0; i < rows.length; i++) {
        const row = rows[i];
        const firstCell = row[0] || '';
        
        // Check if this is a table header
        if (firstCell.includes('表') && firstCell.includes('C.')) {
          // Save previous table if it exists
          if (currentTable && currentTable.fields.length > 0) {
            this.tables.set(currentTable.tableCode, currentTable);
            this.statistics.totalTables++;
          }
          
          const tableInfo = this.extractTableInfo(firstCell);
          if (tableInfo) {
            currentTable = {
              ...tableInfo,
              fileType,
              fields: [],
              rawHeader: firstCell
            };
            console.log(`  📋 Found table: ${tableInfo.tableCode} - ${tableInfo.tableName}`);
          }
          continue;
        }
        
        // Check if this is a field header row
        if (currentTable && row.includes('序号') && row.includes('中文名称')) {
          continue; // Skip header row
        }
        
        // Process field row
        if (currentTable && row.length >= 6) {
          const sequenceNum = row[0];
          const fieldName = row[1];
          const description = row[2];
          const dataType = row[3];
          const measurementType = row[4];
          const dataSource = row[5];
          
          // Validate this is a field row (sequence number should be numeric)
          if (sequenceNum && sequenceNum.match(/^\d+$/) && fieldName && fieldName.trim().length > 0) {
            const field = {
              sequenceNumber: sequenceNum,
              fieldName: fieldName.trim(),
              description: description ? description.trim() : '',
              dataType: dataType ? dataType.trim() : '',
              measurementType: measurementType ? measurementType.trim() : '',
              dataSource: dataSource ? dataSource.trim() : '',
              sqlDataType: DATA_TYPE_MAPPING[dataType?.trim()] || 'TEXT',
              dataSourceDescription: DATA_SOURCE_MAPPING[dataSource?.trim()] || dataSource?.trim() || 'Unknown'
            };
            
            currentTable.fields.push(field);
            
            // Update statistics
            this.updateStatistics(field);
          }
        }
      }
      
      // Save the last table if it exists
      if (currentTable && currentTable.fields.length > 0) {
        this.tables.set(currentTable.tableCode, currentTable);
        this.statistics.totalTables++;
      }
      
      console.log(`  ✅ Processed ${this.tables.size - beforeCount} tables from ${fileType}`);
      
    } catch (error) {
      console.error(`❌ Error processing ${filePath}:`, error.message);
    }
  }

  /**
   * Update statistics with field information
   */
  updateStatistics(field) {
    this.statistics.totalFields++;
    
    // Count data sources
    const source = field.dataSource;
    this.statistics.dataSourceCounts[source] = (this.statistics.dataSourceCounts[source] || 0) + 1;
    
    // Count data types
    const type = field.dataType;
    this.statistics.dataTypeCounts[type] = (this.statistics.dataTypeCounts[type] || 0) + 1;
    
    // Count measurement types
    const measurement = field.measurementType;
    if (measurement && measurement !== '—' && measurement !== '') {
      this.statistics.measurementTypeCounts[measurement] = (this.statistics.measurementTypeCounts[measurement] || 0) + 1;
    }
  }

  /**
   * Generate comprehensive analysis report
   */
  generateReport() {
    console.log('\n' + '='.repeat(80));
    console.log('📊 COMPREHENSIVE FIELD DEFINITIONS ANALYSIS REPORT');
    console.log('='.repeat(80));
    
    // Summary statistics
    console.log('\n📈 SUMMARY STATISTICS');
    console.log('-'.repeat(40));
    console.log(`Total Tables: ${this.statistics.totalTables}`);
    console.log(`Total Fields: ${this.statistics.totalFields}`);
    console.log(`Average Fields per Table: ${(this.statistics.totalFields / this.statistics.totalTables).toFixed(2)}`);
    
    // Data source distribution
    console.log('\n🎯 DATA SOURCE DISTRIBUTION');
    console.log('-'.repeat(40));
    Object.entries(this.statistics.dataSourceCounts)
      .sort(([,a], [,b]) => b - a)
      .forEach(([source, count]) => {
        const percentage = ((count / this.statistics.totalFields) * 100).toFixed(1);
        const description = DATA_SOURCE_MAPPING[source] || source;
        console.log(`${source.padEnd(3)} | ${count.toString().padStart(4)} fields (${percentage.padStart(5)}%) | ${description}`);
      });
    
    // Data type distribution
    console.log('\n📋 DATA TYPE DISTRIBUTION');
    console.log('-'.repeat(40));
    Object.entries(this.statistics.dataTypeCounts)
      .sort(([,a], [,b]) => b - a)
      .forEach(([type, count]) => {
        const percentage = ((count / this.statistics.totalFields) * 100).toFixed(1);
        const sqlType = DATA_TYPE_MAPPING[type] || 'TEXT';
        console.log(`${type.padEnd(6)} | ${count.toString().padStart(4)} fields (${percentage.padStart(5)}%) | SQL: ${sqlType}`);
      });
    
    // Measurement types
    console.log('\n📏 MEASUREMENT TYPES (Top 10)');
    console.log('-'.repeat(40));
    Object.entries(this.statistics.measurementTypeCounts)
      .sort(([,a], [,b]) => b - a)
      .slice(0, 10)
      .forEach(([measurement, count]) => {
        console.log(`${measurement.padEnd(15)} | ${count.toString().padStart(3)} fields`);
      });
  }

  /**
   * Generate detailed table structure report
   */
  generateTableStructures() {
    console.log('\n' + '='.repeat(80));
    console.log('📋 DETAILED TABLE STRUCTURES');
    console.log('='.repeat(80));
    
    // Sort tables by table code
    const sortedTables = Array.from(this.tables.values()).sort((a, b) => a.tableCode.localeCompare(b.tableCode));
    
    sortedTables.forEach((table, index) => {
      console.log(`\n${(index + 1).toString().padStart(2)}. TABLE: ${table.tableCode} - ${table.tableName}`);
      console.log('─'.repeat(78));
      console.log(`   Table Number: ${table.tableNumber}`);
      console.log(`   Full Table Name: ${table.fullTableName}`);
      console.log(`   File Type: ${table.fileType}`);
      console.log(`   Total Fields: ${table.fields.length}`);
      console.log(`   Raw Header: ${table.rawHeader}`);
      
      console.log('\n   FIELDS:');
      table.fields.forEach((field, fieldIndex) => {
        const seq = field.sequenceNumber.padStart(3);
        const name = field.fieldName.substring(0, 25).padEnd(25);
        const type = field.dataType.padEnd(6);
        const source = field.dataSource.padEnd(3);
        const measurement = field.measurementType === '—' ? '' : field.measurementType;
        
        console.log(`   ${seq} | ${name} | ${type} | ${source} | ${measurement}`);
        
        if (field.description && field.description !== '—') {
          const desc = field.description.substring(0, 60);
          console.log(`       Description: ${desc}${field.description.length > 60 ? '...' : ''}`);
        }
      });
    });
  }

  /**
   * Generate SQL CREATE TABLE statements
   */
  generateSQLStatements() {
    console.log('\n' + '='.repeat(80));
    console.log('🔧 SQL CREATE TABLE STATEMENTS');
    console.log('='.repeat(80));
    
    const sortedTables = Array.from(this.tables.values()).sort((a, b) => a.tableCode.localeCompare(b.tableCode));
    
    sortedTables.forEach(table => {
      console.log(`\n-- Table: ${table.tableCode} - ${table.tableName}`);
      console.log(`-- Fields: ${table.fields.length} | File: ${table.fileType}`);
      console.log(`CREATE TABLE IF NOT EXISTS ${table.fullTableName} (`);
      console.log(`    id INTEGER PRIMARY KEY AUTOINCREMENT,`);
      
      table.fields.forEach((field, index) => {
        const columnName = field.fieldName.replace(/\s+/g, '_').replace(/[^\w\u4e00-\u9fff]/g, '');
        const sqlType = field.sqlDataType;
        const comment = field.description ? ` -- ${field.description}` : '';
        const comma = index < table.fields.length - 1 ? ',' : '';
        
        console.log(`    ${columnName} ${sqlType}${comma}${comment}`);
      });
      
      console.log(`);`);
      console.log('');
    });
  }

  /**
   * Generate field metadata for database insertion
   */
  generateFieldMetadata() {
    console.log('\n' + '='.repeat(80));
    console.log('📝 FIELD METADATA FOR DATABASE');
    console.log('='.repeat(80));
    
    const metadata = [];
    
    this.tables.forEach(table => {
      table.fields.forEach((field, index) => {
        metadata.push({
          table_name: table.fullTableName,
          table_code: table.tableCode,
          table_display_name: table.tableName,
          field_name: field.fieldName,
          field_sequence: parseInt(field.sequenceNumber) || index + 1,
          data_type: field.dataType,
          sql_data_type: field.sqlDataType,
          measurement_type: field.measurementType === '—' ? null : field.measurementType,
          data_source: field.dataSource,
          data_source_description: field.dataSourceDescription,
          description: field.description === '—' ? null : field.description,
          file_type: table.fileType
        });
      });
    });
    
    console.log(`Generated ${metadata.length} field metadata records`);
    
    // Save to JSON file
    const outputPath = '/home/abc/delivery-record-fronted/field-metadata-analysis.json';
    fs.writeFileSync(outputPath, JSON.stringify({
      analysis_date: new Date().toISOString(),
      statistics: this.statistics,
      tables: Array.from(this.tables.values()),
      field_metadata: metadata
    }, null, 2));
    
    console.log(`\n💾 Complete analysis saved to: ${outputPath}`);
  }

  /**
   * Validate against database schema
   */
  async validateAgainstDatabase() {
    console.log('\n' + '='.repeat(80));
    console.log('🔍 DATABASE VALIDATION');
    console.log('='.repeat(80));
    
    try {
      // This would connect to the actual database and compare
      // For now, we'll just report what we found
      console.log('📊 ANALYSIS SUMMARY FOR DATABASE VERIFICATION:');
      console.log(`- Found ${this.statistics.totalTables} parameter tables`);
      console.log(`- Total field definitions: ${this.statistics.totalFields}`);
      console.log(`- Data sources: ${Object.keys(this.statistics.dataSourceCounts).join(', ')}`);
      console.log(`- Data types: ${Object.keys(this.statistics.dataTypeCounts).join(', ')}`);
      
      console.log('\n🎯 RECOMMENDED NEXT STEPS:');
      console.log('1. Compare this analysis with your database schema');
      console.log('2. Verify all 78 parameter tables are represented');
      console.log('3. Check field permissions align with data sources');
      console.log('4. Validate field data types match database columns');
      console.log('5. Ensure measurement units are properly mapped');
      
    } catch (error) {
      console.error('❌ Database validation error:', error.message);
    }
  }

  /**
   * Run complete analysis
   */
  async run() {
    console.log('🚀 Starting Comprehensive Field Definitions Analysis...');
    
    // Process all files
    this.processFile(FILES.base, 'Base Parameters');
    this.processFile(FILES.naming, 'Object Naming');
    this.processFile(FILES.position, 'Position Information');
    
    // Generate reports
    this.generateReport();
    this.generateTableStructures();
    this.generateSQLStatements();
    this.generateFieldMetadata();
    await this.validateAgainstDatabase();
    
    console.log('\n✅ Analysis complete!');
  }
}

// Run the analysis
if (require.main === module) {
  const analyzer = new FieldDefinitionAnalyzer();
  analyzer.run().catch(console.error);
}

module.exports = FieldDefinitionAnalyzer;